Excel Workbook by Clerici Alberto; Del Corno Davide;

Author:Clerici, Alberto; Del Corno, Davide;
Language: eng
Format: epub, pdf
Publisher: EGEA Spa - Bocconi University Press

EXERCISE 10.9 - Albert Einstein

Open the file Exercise 10.9 - Albert Einstein.xlsx. The file shows the grades obtained in mathematics by students of the 4th H of high school Albert Einstein. Your friend Mario, in fact, teaches mathematics, but he is not very good with computers, and would like to automate a series of operations on the register of votes. For that, he asks for your help.

In particular, he asks you to help him solve the following problems:

1. Calculate, in cells F4:F23 and J4:J23, the average per four months. In cells K4:K23, calculate the final average, as the average of the two four months’ averages.

2. In cells L4:L23, enter a function that returns the final result achieved by each student, as follows:

• Promoted, if the final average is greater than or equal to 6

• Postponed, if the final average is between 5 (included) and 6 (not included)

• Rejected if the final average is less than 5

3. Format the cells in the Result, so that the three results are automatically formatted with a different fill color (e.g., green for Promoted, yellow for Postponed, red for Rejected). Formatting should update automatically as data change.

4. Rename Sheet1 with the name 4H.

5. Record a macro in the current workbook, named Sort_students, which sorts students in an ascending order according to the last name, then the first name simultaneously. Save the file in the correct format to contain macros, as Exercise 10.9 - Einstein macro.xlsm.

6. Create a button using a WordArt at will, with the text “Sort”. Insert a rectangle around the WordArt. Group the two objects. Assign the macro you just created to the grouped object.

7. Add a column after the Name, with header Full name. In this column, enter a function to return the last name and the name of each student together, separated by a space.

8. Insert a 3D grouped bar chart that shows the Full names of the students and the final average. Change the fill color of the bar relative to the highest average (e.g., in green) and to the lowest one (e.g., in red).

9. Set the image Exercise 10.9 - Marks.jpg as the chart background, setting transparency at 75%.

10. Insert a new column to the left of that of the last name. Add the header “Code” in cell B3. In cells B4:B23, enter a function to build a code for each student, in the following way (all caps):

• The first 3 characters of the Last name

• The number of characters of the Last name

• The last 2 characters of the Name

11. Enter a comment in cell B3, which explains how the code has been created.

12. In cell A26, enter the text “List updated:”. In cell D26, enter a function that states the current date, so that it is automatically updated as the date changes.

13. In cell A27, enter the text “Number of students:”. In the cell D27, count the number of students in the class, using appropriate Excel function.

14. Protect the workbook so that it can only be opened by entering the password “GradEs4H”.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.